Snowflakeの検索最適化サービス(Search Optimization Service)を試してみた
こんにちは！DA(データアナリティクス)事業本部 インテグレーション部の大高です。
SnowflakeのEnterprise Edition以上で利用できる機能として「検索最適化サービス(Search Optimization Service)」という機能があります。
今回はこの機能について調べてみて、実際に試してみました。
検索最適化サービス(Search Optimization Service) とは？
検索最適化サービスは、大きなテーブルにおける選択的な検索クエリのパフォーマンスを向上させる機能です。たとえば、10億件あるテーブルの中から1行〜数行のレコードを抽出するようなSELECT文のパフォーマンスが、検索最適化サービスを利用することでパフォーマンス向上させることができます。
メリットを受けるケース
検索最適化サービスのメリットを受けるケースは下記ドキュメントに記載のとおりです。
主なものは以下のとおりです。サイズの大きなテーブルで、非クラスター化テーブルに対するクエリが主に検討対象になりそうですね。
- テーブルサイズが 100GB 以上ある
- 非クラスター化テーブルである
- メインクラスターキー以外の列で頻繁に検索される
- 数十秒実行されるクエリを実行する
- クエリフィルターでアクセスされる列の少なくとも1つに、10万〜20万以上の個別の値がある
- 等価述語
<列名> = <定数>やINを使用する述語を利用している
利用コストについて
検索最適化サービスは、検索アクセスパスの「ストレージコスト」および検索最適の追加・維持における「コンピューティングコスト」の2つのコストが掛かります。
こちらも詳細はこちらのドキュメントに記載の通りです。
ドキュメントに記載がありますが、SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTSを利用すると推定コストを算出することができます。実際に出してみると以下のようになりました。
SELECT SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('customer');
{
"tableName" : "CUSTOMER",
"searchOptimizationEnabled" : false,
"costPositions" : [ {
"name" : "BuildCosts",
"costs" : {
"value" : 8.971,
"unit" : "Credits"
},
"computationMethod" : "EstimatedUpperBound"
}, {
"name" : "StorageCosts",
"costs" : {
"value" : 0.049834,
"unit" : "TB"
},
"computationMethod" : "EstimatedUpperBound"
}, {
"name" : "MaintenanceCosts",
"computationMethod" : "NotAvailable",
"comment" : "Insufficient data to compute estimate for maintenance cost. Table is too young. Requires 7 day(s) of history."
} ]
}
BuildCostsが構築時の推定コストです。今回検証するテーブルデータの場合、8.971クレジットが掛かります。また、StorageCostsが検索アクセスパスに必要なストレージの推定コストになります。こちらは0.049834クレジットでした。
また、今回はテーブルを作成したばかりなので表示されませんでしたが、MaintenanceCostsに維持のための推定コストが表示されます。
検索最適化の利用には上記のようなコストがかかりますので、「コストを上回るメリットがあるかどうか」を検討の上で利用することになるかと思います。
制限事項（2021/03/03 現在）
制限事項としては以下があります。
- サポート対象外
- 外部テーブル
- マテリアライズドビュー
- COLLATE句で定義された列
- 列の連結
- 分析表現
- テーブル列へのキャスト
「テーブル列へのキャスト」は通常のインデックスを利用した検索でもよく言われる「Where句で指定するカラムをCASTしない」というものですね。
また、サポートされているデータ型は以下になります。
- 固定小数点数（例： INTEGER、NUMERIC）
- DATE、 TIME、 TIMESTAMP
- VARCHAR
- BINARY
不動小数点型のFLOATや、半構造化データ型のVARIANTなどは、現在非サポートです。
実際に検索最適化サービスを試してみた
では、実際に検索最適化サービスが有用となる大きなサイズのテーブルデータで試してみたいと思います。
テスト用テーブルは約100GBのテーブルsnowflake_sample_data.tpch_sf10000.customerを利用します。また、検索クエリとしてはcustomer.c_custkeyカラムに対して等価述語で検索をかけるクエリを利用します。
まずは以下のようにテスト用テーブルを準備します。なお、ロール、ウェアハウス、DBについては事前に作成しておいたものを利用しています。
-- ロール、ウェアハウス、DBを指定 USE ROLE OOTAKA_SANDBOX_ROLE; USE WAREHOUSE LARGE_WH; USE DATABASE OOTAKA_SANDBOX_DB; -------------------------------------------------- -- テスト用テーブルの準備 -------------------------------------------------- -- テスト用テーブルの元データを確認 -- カラムにおける個別の値の概算値をSELECT SELECT APPROX_COUNT_DISTINCT(c_custkey) FROM snowflake_sample_data.tpch_sf10000.customer; -- 1,523,662,319 => 10万〜20万以上なのでOK -- スキーマを新規作成 CREATE SCHEMA SOS; -- スキーマを指定 USE SCHEMA SOS; -- テスト用テーブルをCTASで作成 CREATE TABLE customer AS SELECT * FROM snowflake_sample_data.tpch_sf10000.customer;
これでテスト用テーブルが作成できました。APPROX_COUNT_DISTINCTを利用してc_custkeyカラムの値が10万〜20万以上ということも確認しています。
次に、検索最適化を有効化します。
-------------------------------------------------- -- 検索最適化の有効化 -------------------------------------------------- -- テスト用テーブルの検索最適化を有効化 ALTER TABLE customer ADD SEARCH OPTIMIZATION; -- 検索最適化の確認 -- SEARCH_OPTIMIZATION が ON であることを確認 -- SEARCH_OPTIMIZATION_PROGRESS が 100 であることを確認 SHOW TABLES LIKE '%customer%';
適用後、しばらくするとSEARCH_OPTIMIZATION_PROGRESSが100になるので適用完了後に実施に検索クエリを流して、どのような違いがでるか試してみます。
-------------------------------------------------- -- 検索最適化の検証 -------------------------------------------------- -- 検索最適化を有効化したテーブルでc_custkeyを指定してSELECT SELECT c_custkey, c_name FROM customer WHERE c_custkey = 471011441 ; -- 1.77s -- 検索最適化を有効化していないテーブルでc_custkeyを指定してSELECT SELECT c_custkey, c_name FROM snowflake_sample_data.tpch_sf10000.customer WHERE c_custkey = 471011441 ; -- 3.77s
今回はLARGEサイズのウェアハウスを利用しているので、どちらもそれなりに早いのですが2倍ぐらいの速度で検索できています。
また、クエリのプロファイルはそれぞれ以下のようになっており、ちゃんと検索最適化が利用されているのがわかります。（スキャンが圧倒的に改善されていますね）
まとめ
以上、Snowflakeの検索最適化サービス(Search Optimization Service)を試してみました。データ量が多いテーブルにおいてSELECTの速度が遅くて困っている場合などに役立ちそうな機能なので覚えておきたいと思います。
どなたかのお役に立てば幸いです。それでは！
![[新機能]dbtのdata testで任意の設定をconfigで指定できるようになり、各data testでSnowflakeのウェアハウスを指定できるようになりました](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-f846b6052d1f7b983172226c6b1ed44b/6b4729aabfb5b7a3359acc27dc6b5453/dbt-1200x630-1.jpg)
